from generate_common import handleCustomApply

# genItemUsage
def genItemUsage(ruleDatum, ruleAct, filterDatum, hsp_abbr):
    # 获得ruleAct中的trigger_apply，并且制作成为sql语句
    triggerApplyObj = ruleAct.get('trigger_apply', [])
    triggerSql, _ = handleCustomApply(triggerApplyObj, type='item', tblAbbr='sie')

    # 获得 filterDatum当中的关键字段
    operator = filterDatum['operator']
    value = filterDatum['value']

    # 尝试获取 of 与 by。避免与其他字段冲突
    applyOf = filterDatum.get('of', ['in','out'])
    applyBy = filterDatum.get('by', 'default')

    # 针对applyOf的情况，制作applyOfSql
    if len(applyOf) == 1:
        if applyOf[0] == 'in':
            applyOfSql = f"AND sie.j_isin='1'\n"
        elif applyOf[0] == 'out':
            applyOfSql = f"AND sie.j_isin='0'\n"
    else:
        applyOfSql = ""
    
    # 针对operator的情况，制作operSql
    if operator == 'eq':
        operSql = "="
    elif operator == 'gt':
        operSql = ">"
    elif operator == 'lt':
        operSql = "<"
    elif operator == 'gte':
        operSql = ">="
    elif operator == 'lte':
        operSql = "<="

    # ============ 思路 ============
    # 1. 应用 sie 表，关联对应的 j_year 与 j_isin，获得项目使用率，放在 vb 表中，并且应用筛选条件，只保留违规条件
    #     a. 如果是 by = default，那么保留的 item_j_code, j_year, j_isin 就是违规的
    #     b. 如果 by = aggr，那么保留的 j_year, j_isin 就是违规的
    # 2. va 表中删除 vb 表中没有保留的部分
    # ============ 思路 END ============

    sqlList = [
        # 清空临时表
        "TRUNCATE TABLE vlt_tmp_b"
    ]

    # 第1步
    if applyBy == 'default':
        sql1 = f"""
            -- item usage by default，应用 sie 表，关联对应的 j_year 与 j_isin，获得项目使用率，放在 vb 表中，并且应用筛选条件，只保留违规条件
            INSERT INTO vlt_tmp_b (j_year, j_isin, item_j_code, num1)
            SELECT sie.j_year, sie.j_isin, sie.item_j_code, MAX(sie.usage) as num1
            FROM vlt_tmp_a va, scene_item_ext sie
            WHERE va.j_year = sie.j_year AND va.j_isin = sie.j_isin AND va.item_j_code = sie.item_j_code
            GROUP BY sie.j_year, sie.j_isin, sie.item_j_code
            HAVING MAX(sie.usage) {operSql} {value}
        """
    elif applyBy in ('max','min','avg'):
        sql1 = f"""
            -- item usage by aggr，应用 sie 表，关联对应的 j_year 与 j_isin，获得项目使用率，放在 vb 表中，并且应用筛选条件，只保留违规条件
            INSERT INTO vlt_tmp_b (j_year, j_isin, num1)
            SELECT sie.j_year, sie.j_isin, {applyBy}(sie.usage) as num1
            FROM vlt_tmp_a va, scene_item_ext sie
            WHERE va.j_year = sie.j_year AND va.j_isin = sie.j_isin AND va.item_j_code = sie.item_j_code
            GROUP BY sie.j_year, sie.j_isin
            HAVING {applyBy}(sie.usage) {operSql} {value}
        """
    else:
        # 不合法的 by 值，报错
        raise ValueError(f"itemUsage，Invalid applyBy value: {applyBy}")

    # 将sql1添加到sqlList
    sqlList.append(sql1)

    # 第2步
    sql2 = f"""
        -- 删除 vlt_tmp_a 中不再符合条件的记录
        DELETE FROM vlt_tmp_a va
        WHERE NOT EXISTS (
            SELECT 1
            FROM vlt_tmp_b vb
            WHERE va.j_year = vb.j_year
              AND va.j_isin = vb.j_isin
              AND (vb.item_j_code is not null and va.item_j_code = vb.item_j_code)
        )
    """
    sqlList.append(sql2)
    return sqlList
